CREATE PROCEDURE [dbo].[utl_SetupPortalPermissions]

AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @CreateUserScript nvarchar(1024)
	DECLARE @ConnectScript nvarchar(1024)

	--1.  DROP ROLES AND ROLE MEMBERS
	EXEC [dbo].[utl_DropRole] @RoleName='ESBPortal'
	EXEC [dbo].[utl_DropRole] @RoleName='ESBPortalAdmin'
	EXEC [dbo].[utl_DropRole] @RoleName='ESBSubmitNewFaults'
	EXEC [dbo].[utl_DropRole] @RoleName='ESBBrowseAndManageFaults'
	EXEC [dbo].[utl_DropRole] @RoleName='ESBSendNotifications'

	--2. CREATE ROLES
	CREATE ROLE [ESBSubmitNewFaults] AUTHORIZATION [dbo]
	CREATE ROLE [ESBBrowseAndManageFaults] AUTHORIZATION [dbo]
	CREATE ROLE [ESBSendNotifications] Authorization [dbo]

	--3. ADD ROLE Members
	DECLARE @BizTalkInProcessHostUsers nvarchar(50)
	--Get the login name from the local environment
	SELECT @BizTalkInProcessHostUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%HQ-BT-AppUsers%'
	--Check if login is user in the database - if not, then add it
	IF NOT EXISTS     
	(SELECT [NAME] FROM sys.database_principals WHERE name = @BizTalkInProcessHostUsers) 
	 BEGIN
		SET @CreateUserScript = N'CREATE USER [' + @BizTalkInProcessHostUsers + N'] FOR LOGIN [' + @BizTalkInProcessHostUsers + ']'
		EXEC (@CreateUserScript)
		SET @ConnectScript = N'GRANT CONNECT TO [' + @BizTalkInProcessHostUsers + ']'
		EXEC(@ConnectScript)
	END
	--Add user to role
	EXEC sys.sp_addrolemember @rolename=N'ESBSubmitNewFaults', @membername=@BizTalkInProcessHostUsers
	

	DECLARE @IISServicesUsers nvarchar(50)
	--Get the login name from the local environment
	SELECT @IISServicesUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%svcEsb%'
	--Check if login is user in the database - if not, then add it
	IF NOT EXISTS     
	(SELECT [NAME] FROM sys.database_principals WHERE name = @IISServicesUsers) 
	 BEGIN
		SET @CreateUserScript = N'CREATE USER [' + @IISServicesUsers + N'] FOR LOGIN [' + @IISServicesUsers + ']'
		EXEC (@CreateUserScript)
		SET @ConnectScript = N'GRANT CONNECT TO [' + @IISServicesUsers + ']'
		EXEC(@ConnectScript)
	END
	--Add user to role
	EXEC sys.sp_addrolemember @rolename=N'ESBBrowseAndManageFaults', @membername=@IISServicesUsers
	
	
	--DECLARE @IISAppUsers nvarchar(50)
	----Get the login name from the local environment
	--SELECT @IISAppUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%svcIIS%Application%'
	----Check if login is user in the database - if not, then add it
	--IF NOT EXISTS     
	--(SELECT [NAME] FROM sys.database_principals WHERE name = @IISAppUsers) 
	-- BEGIN
	--	SET @CreateUserScript = N'CREATE USER [' + @IISAppUsers + N'] FOR LOGIN [' + @IISAppUsers + ']'
	--	EXEC (@CreateUserScript)
	--	SET @ConnectScript = N'GRANT CONNECT TO [' + @IISAppUsers + ']'
	--	EXEC(@ConnectScript)
	--END
	----Add user to role
	--EXEC sys.sp_addrolemember @rolename=N'ESBBrowseAndManageFaults', @membername=@IISAppUsers 
	
	

	DECLARE @ESBExceptionNotificationUsers nvarchar(50)
	--Get the login name from the local environment
	SELECT @ESBExceptionNotificationUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%svcEsb%'
	--Check if login is user in the database - if not, then add it
	IF NOT EXISTS     
	(SELECT [NAME] FROM sys.database_principals WHERE name = @ESBExceptionNotificationUsers) 
	 BEGIN
		SET @CreateUserScript = N'CREATE USER [' + @ESBExceptionNotificationUsers + N'] FOR LOGIN [' + @ESBExceptionNotificationUsers + ']'
		EXEC (@CreateUserScript)
		SET @ConnectScript = N'GRANT CONNECT TO [' + @ESBExceptionNotificationUsers + ']'
		EXEC(@ConnectScript)
	END
	--Add user to role
	EXEC sys.sp_addrolemember @rolename=N'ESBSendNotifications', @membername=@ESBExceptionNotificationUsers 
	

	--4. For SP's - assign Execute to the Submit New Faults Role.
	GRANT EXECUTE ON [dbo].[usp_insert_ContextProperty] To ESBSubmitNewFaults
	GRANT EXECUTE ON [dbo].[usp_insert_Fault] To ESBSubmitNewFaults
	GRANT EXECUTE ON [dbo].[usp_insert_Message] To ESBSubmitNewFaults
	
	-- For ESB notifications set execute permissions on SP's
	GRANT EXECUTE ON [dbo].[usp_select_Active_AlertSubscriptions] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_select_AlertEmail] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_insert_AlertEmail] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_update_AlertEmail] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_insert_AlertHistory] To ESBSendNotifications	
	GRANT EXECUTE ON [dbo].[usp_select_Alerts] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_insert_AlertSubscriptionHistory] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_insert_Batch] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_update_Batch] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_select_Configuration] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_select_FaultsToQueue] To ESBSendNotifications
	GRANT EXECUTE ON [dbo].[usp_insert_ProcessedFault] To ESBSendNotifications
	
	GRANT SELECT ON [dbo].[Alert] to ESBSendNotifications

	GRANT SELECT ON [dbo].[AlertEmail] to ESBSendNotifications
	GRANT INSERT ON [dbo].[AlertEmail] to ESBSendNotifications
	GRANT UPDATE ON [dbo].[AlertEmail] to ESBSendNotifications
	GRANT DELETE ON [dbo].[AlertEmail] to ESBSendNotifications
	
	GRANT SELECT ON [dbo].[AlertHistory] to ESBSendNotifications
	GRANT INSERT ON [dbo].[AlertHistory] to ESBSendNotifications
	GRANT UPDATE ON [dbo].[AlertHistory] to ESBSendNotifications
	GRANT DELETE ON [dbo].[AlertHistory] to ESBSendNotifications
	
	GRANT SELECT ON [dbo].[AlertSubscription] to ESBSendNotifications
	GRANT SELECT ON [dbo].[AlertSubscriptionHistory] to ESBSendNotifications

	GRANT SELECT ON [dbo].[Batch] to ESBSendNotifications
	GRANT INSERT ON [dbo].[Batch] to ESBSendNotifications
	GRANT UPDATE ON [dbo].[Batch] to ESBSendNotifications
	GRANT DELETE ON [dbo].[Batch] to ESBSendNotifications
	
	GRANT SELECT ON [dbo].[Fault] to ESBSendNotifications
	GRANT INSERT ON [dbo].[Fault] to ESBSendNotifications
	GRANT UPDATE ON [dbo].[Fault] to ESBSendNotifications
	GRANT DELETE ON [dbo].[Fault] to ESBSendNotifications
	
	GRANT SELECT ON [dbo].[ProcessedFault] to ESBSendNotifications
	GRANT INSERT ON [dbo].[ProcessedFault] to ESBSendNotifications
	GRANT UPDATE ON [dbo].[ProcessedFault] to ESBSendNotifications
	GRANT DELETE ON [dbo].[ProcessedFault] to ESBSendNotifications
	
	--5. For each table (non system) in database, assign select to BrowseAndManage Role
	BEGIN
		DECLARE @TableName		sysname
		DECLARE @TableID        INT
		DECLARE @SQL      NVARCHAR(MAX)
		
		DECLARE @Tables TABLE (TableID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, TableName sysname)
		
		INSERT INTO @Tables
		  (
			TableName
		  )
		SELECT TableName
		FROM   dbo.fn_Tables()
		
		SELECT @TableID = MIN(TableID)
		FROM   @Tables
		
		WHILE @TableID IS NOT NULL
		BEGIN
			SELECT @TableName = TableName
			FROM   @Tables
			WHERE  TableID = @TableID
		    
			SET @SQL = 'GRANT SELECT ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
			EXECUTE sp_executesql @SQL

			SET @SQL = 'GRANT INSERT ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
			EXECUTE sp_executesql @SQL

			SET @SQL = 'GRANT UPDATE ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
			EXECUTE sp_executesql @SQL

			SET @SQL = 'GRANT DELETE ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
			EXECUTE sp_executesql @SQL
		    
			SELECT @TableID = MIN(TableID)
			FROM   @Tables
			WHERE  TableID > @TableID
		END
	END


	--6. For each proc (non system) in database, assign exec to BrowsAndManageRole roles
	BEGIN
		DECLARE @ProcName		sysname
		DECLARE @ProcID         INT
		DECLARE @ExecSQL        NVARCHAR(MAX)
		
		DECLARE @Procs TABLE (ProcID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, ProcName sysname)
		
		INSERT INTO @Procs
		  (
			ProcName
		  )
		SELECT ProcedureName as ProcName
		FROM   dbo.fn_CustomProcs()
		
		SELECT @ProcID = MIN(ProcID)
		FROM   @Procs
		
		WHILE @ProcID IS NOT NULL
		BEGIN
			SELECT @ProcName = ProcName
			FROM   @Procs
			WHERE  ProcID = @ProcID
		    
			SET @ExecSQL = 'GRANT EXECUTE ON ' + dbo.fn_QuoteName(@ProcName) + ' TO [ESBBrowseAndManageFaults]'
			EXECUTE sp_executesql @ExecSQL
		    
			SELECT @PROCID = MIN(ProcID)
			FROM   @Procs
			WHERE  ProcID > @ProcID
		END
	END
	
		
	--7. Assign select permission to BrowseAndManage Role to these views
	GRANT SELECT ON dbo.vw_AggregatedFaults TO ESBBrowseAndManageFaults
	GRANT SELECT ON dbo.vw_UserApplication TO ESBBrowseAndManageFaults
END